import { getAllKeywords } from './sqlKeywords'

/**
 * SQL验证结果
 * @typedef {Object} ValidationResult
 * @property {boolean} isValid - 是否有效
 * @property {Array} errors - 错误列表
 * @property {Array} warnings - 警告列表
 */

/**
 * 验证SQL语法
 * @param {string} sql - 要验证的SQL语句
 * @param {string} dbType - 数据库类型 (mysql, postgresql, sqlserver, sqlite, oracle)
 * @returns {ValidationResult} 验证结果
 */
export const validateSql = (sql, dbType = '') => {
  const result = {
    isValid: true,
    errors: [],
    warnings: []
  }

  if (!sql || typeof sql !== 'string') {
    result.isValid = false
    result.errors.push('SQL语句不能为空')
    return result
  }

  const trimmedSql = sql.trim()
  
  // 基本语法检查
  const basicChecks = [
    checkParentheses,
    checkQuotes,
    checkBasicSyntax,
    checkSemicolon
  ]

  for (const check of basicChecks) {
    const checkResult = check(trimmedSql, dbType)
    if (checkResult.errors.length > 0) {
      result.errors.push(...checkResult.errors)
      result.isValid = false
    }
    if (checkResult.warnings.length > 0) {
      result.warnings.push(...checkResult.warnings)
    }
  }

  // 特定数据库的语法检查
  if (dbType) {
    const dbSpecificCheck = getDbSpecificValidator(dbType)
    if (dbSpecificCheck) {
      const dbCheckResult = dbSpecificCheck(trimmedSql)
      if (dbCheckResult.errors.length > 0) {
        result.errors.push(...dbCheckResult.errors)
        result.isValid = false
      }
      if (dbCheckResult.warnings.length > 0) {
        result.warnings.push(...dbCheckResult.warnings)
      }
    }
  }

  return result
}

/**
 * 获取数据库特定的验证器
 */
const getDbSpecificValidator = (dbType) => {
  const dbTypeLower = dbType.toLowerCase()
  
  switch (dbTypeLower) {
    case 'mysql':
      return checkMySqlSyntax
    case 'postgresql':
      return checkPostgreSqlSyntax
    case 'sqlserver':
      return checkSqlServerSyntax
    case 'sqlite':
      return checkSqliteSyntax
    case 'oracle':
      return checkOracleSyntax
    default:
      return null
  }
}

/**
 * 检查括号匹配
 */
const checkParentheses = (sql) => {
  const result = { errors: [], warnings: [] }
  let count = 0
  
  for (let i = 0; i < sql.length; i++) {
    if (sql[i] === '(') count++
    if (sql[i] === ')') count--
    if (count < 0) {
      result.errors.push('括号不匹配：多余的右括号')
      break
    }
  }
  
  if (count > 0) {
    result.errors.push('括号不匹配：缺少右括号')
  }
  
  return result
}

/**
 * 检查引号匹配
 */
const checkQuotes = (sql) => {
  const result = { errors: [], warnings: [] }
  let singleQuoteCount = 0
  let doubleQuoteCount = 0
  let inSingleQuote = false
  let inDoubleQuote = false
  
  for (let i = 0; i < sql.length; i++) {
    const char = sql[i]
    const prevChar = i > 0 ? sql[i - 1] : ''
    
    if (char === "'" && prevChar !== '\\' && !inDoubleQuote) {
      inSingleQuote = !inSingleQuote
      singleQuoteCount++
    }
    
    if (char === '"' && prevChar !== '\\' && !inSingleQuote) {
      inDoubleQuote = !inDoubleQuote
      doubleQuoteCount++
    }
  }
  
  if (singleQuoteCount % 2 !== 0) {
    result.errors.push('单引号不匹配')
  }
  
  if (doubleQuoteCount % 2 !== 0) {
    result.errors.push('双引号不匹配')
  }
  
  return result
}

/**
 * 检查基本SQL语法
 */
const checkBasicSyntax = (sql, dbType = '') => {
  const result = { errors: [], warnings: [] }
  const upperSql = sql.toUpperCase()
  
  // 检查是否以有效的SQL关键词开始
  const validStarters = ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER', 'WITH']
  const startsWithValid = validStarters.some(starter => upperSql.trim().startsWith(starter))
  
  if (!startsWithValid) {
    result.warnings.push('SQL语句应该以有效的关键词开始')
  }
  
  // 检查SELECT语句的基本结构
  if (upperSql.includes('SELECT')) {
    if (!upperSql.includes('FROM') && !upperSql.includes('DUAL')) {
      // Oracle可以使用DUAL
      if (dbType.toLowerCase() !== 'oracle' || !upperSql.includes('DUAL')) {
        result.warnings.push('SELECT语句通常需要FROM子句')
      }
    }
  }
  
  return result
}

/**
 * 检查分号
 */
const checkSemicolon = (sql, dbType = '') => {
  const result = { errors: [], warnings: [] }
  
  // 在某些数据库中，分号是必须的
  const requiresSemicolon = ['postgresql', 'oracle'].includes(dbType.toLowerCase())
  
  // 检查是否有多个语句但缺少分号分隔
  const statements = sql.split(';').filter(s => s.trim())
  if (statements.length > 1) {
    const lastStatement = statements[statements.length - 1].trim()
    if (lastStatement && !sql.trim().endsWith(';')) {
      result.warnings.push('多个SQL语句应该用分号分隔')
    }
  } else if (requiresSemicolon && !sql.trim().endsWith(';') && sql.trim().length > 0) {
    result.warnings.push(`在${dbType}中，SQL语句应该以分号结束`)
  }
  
  return result
}

/**
 * MySQL特定语法检查
 */
const checkMySqlSyntax = (sql) => {
  const result = { errors: [], warnings: [] }
  const upperSql = sql.toUpperCase()
  
  // 检查USE语句
  if (upperSql.includes('USE ')) {
    const useMatch = upperSql.match(/USE\s+([^\s;]+)/i)
    if (useMatch && useMatch[1] && useMatch[1].includes('.')) {
      result.warnings.push('MySQL的USE语句不应包含点号，应直接使用数据库名')
    }
  }
  
  // 检查LIMIT语法
  if (upperSql.includes('LIMIT')) {
    const limitMatch = upperSql.match(/LIMIT\s+([^,\s;)]+)\s*,\s*([^,\s;)]+)/i)
    if (limitMatch) {
      result.warnings.push('MySQL的LIMIT offset, count语法已过时，建议使用LIMIT count OFFSET offset语法')
    }
  }
  
  return result
}

/**
 * PostgreSQL特定语法检查
 */
const checkPostgreSqlSyntax = (sql) => {
  const result = { errors: [], warnings: [] }
  const upperSql = sql.toUpperCase()
  
  // 检查标识符引用
  if (upperSql.includes('`')) {
    result.warnings.push('PostgreSQL使用双引号"而不是反引号`来引用标识符')
  }
  
  // 检查LIMIT/OFFSET语法
  if (upperSql.includes('LIMIT') && !upperSql.includes('OFFSET') && /LIMIT\s+\d+\s*,\s*\d+/i.test(sql)) {
    result.warnings.push('PostgreSQL不支持LIMIT offset, count语法，应使用LIMIT count OFFSET offset语法')
  }
  
  return result
}

/**
 * SQL Server特定语法检查
 */
const checkSqlServerSyntax = (sql) => {
  const result = { errors: [], warnings: [] }
  const upperSql = sql.toUpperCase()
  
  // 检查TOP语法
  if (upperSql.includes('SELECT') && !upperSql.includes('TOP') && upperSql.includes('LIMIT')) {
    result.warnings.push('SQL Server不支持LIMIT语法，应使用TOP语法或OFFSET/FETCH')
  }
  
  // 检查标识符引用
  if (upperSql.includes('`')) {
    result.warnings.push('SQL Server使用方括号[]或双引号"来引用标识符，不支持反引号`')
  }
  
  return result
}

/**
 * SQLite特定语法检查
 */
const checkSqliteSyntax = (sql) => {
  const result = { errors: [], warnings: [] }
  
  // SQLite相对宽松，但也有一些特有的检查
  if (/AUTOINCREMENT\s+PRIMARY\s+KEY/i.test(sql)) {
    result.warnings.push('SQLite中应使用PRIMARY KEY AUTOINCREMENT而不是AUTOINCREMENT PRIMARY KEY')
  }
  
  return result
}

/**
 * Oracle特定语法检查
 */
const checkOracleSyntax = (sql) => {
  const result = { errors: [], warnings: [] }
  const upperSql = sql.toUpperCase()
  
  // 检查LIMIT语法
  if (upperSql.includes('LIMIT')) {
    result.warnings.push('Oracle不支持LIMIT语法，应使用ROWNUM或ROW_NUMBER()和FETCH FIRST ROWS')
  }
  
  // 检查双引号标识符
  if (upperSql.includes('"') && !/'\w*"\w*'/i.test(sql)) {
    result.warnings.push('Oracle中双引号用于标识符大小写敏感，确保正确使用')
  }
  
  return result
}

/**
 * 获取SQL语句类型
 * @param {string} sql - SQL语句
 * @returns {string} 语句类型
 */
export const getSqlType = (sql) => {
  if (!sql || typeof sql !== 'string') {
    return 'unknown'
  }

  const trimmed = sql.trim().toUpperCase()
  
  if (trimmed.startsWith('SELECT')) return 'select'
  if (trimmed.startsWith('INSERT')) return 'insert'
  if (trimmed.startsWith('UPDATE')) return 'update'
  if (trimmed.startsWith('DELETE')) return 'delete'
  if (trimmed.startsWith('CREATE')) return 'create'
  if (trimmed.startsWith('DROP')) return 'drop'
  if (trimmed.startsWith('ALTER')) return 'alter'
  if (trimmed.startsWith('WITH')) return 'cte'
  
  return 'unknown'
}

/**
 * 检查SQL是否为查询语句（只读）
 * @param {string} sql - SQL语句
 * @returns {boolean} 是否为查询语句
 */
export const isReadOnlyQuery = (sql) => {
  const type = getSqlType(sql)
  return ['select', 'with'].includes(type)
}

export default {
  validateSql,
  getSqlType,
  isReadOnlyQuery
} 